﻿using System;
using System.Collections.Generic;
using System.Text;
using Hanhud.Data;
using System.Data;
using System.Data.SqlClient;

namespace Hanhud.Web.Data.Providers
{
    internal class CWebProvider
    {
        private CDbAccess _db = null;

        internal static CWebProvider Instance
        {
            get { return Hanhud.Pattern.CPageSingleton<CWebProvider>.Instance; }
        }

        public CWebProvider()
        {
            _db = new CDbAccess();
            _db.SetConnectionManagerAdapter<CWebProvider>();
        }

        public DataTable GetWebSettingTable()
        {
            DataTable tblWebSetting = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetCurrentWebSettings"));
            cmd.CommandType = CommandType.StoredProcedure;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;

                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleRow);
                tblWebSetting.Load(reader, LoadOption.Upsert);
            }

            return tblWebSetting;
        }

        public bool UpdateWebSetting(string logo, string slogan, string siteUrl, string siteName, string siteDescription, string address)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("UpdateCurrentSettings"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Logo", SqlDbType.NVarChar, 256).Value = logo;
            cmd.Parameters.Add("@Slogan", SqlDbType.NVarChar, 256).Value = slogan;
            cmd.Parameters.Add("@SiteUrl", SqlDbType.NVarChar, 256).Value = siteUrl;
            cmd.Parameters.Add("@SiteName", SqlDbType.NVarChar, 256).Value = siteName;
            cmd.Parameters.Add("@SiteDescription", SqlDbType.NVarChar, 500).Value = siteDescription;
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 500).Value = address;

            int retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        public string GetContactInfo()
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ContactInfo_GetContactInfo"));
            cmd.CommandType = CommandType.StoredProcedure;

            object contactInfo =  _db.ExecuteSalar(cmd);
            return contactInfo.ToString();
        }

        public bool UpdateContactInfo(string info)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ContactInfo_UpdateContactInfo"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ContactInfo", SqlDbType.NVarChar, 4000).Value = info;
            int retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        public DataRow GetProfile(string id, bool primary)
        {
            DataTable tblProfile = new DataTable();
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("CompanyProfiles_GetProfile"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ID", SqlDbType.VarChar, 50).Value = id;
            cmd.Parameters.Add("@Primary", SqlDbType.Bit).Value = primary;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleRow);
                tblProfile.Load(reader, LoadOption.Upsert);
                reader.Close();
            }

            return tblProfile.Rows.Count > 0 ? tblProfile.Rows[0] : null;
        }

        public bool UpdateProfile(string id, string name, string body, bool primary)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("CompanyProfiles_UpdateProfile"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ID", SqlDbType.VarChar, 50).Value = id;
            cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = name;
            cmd.Parameters.Add("@Body", SqlDbType.NVarChar, int.MaxValue).Value = body;
            cmd.Parameters.Add("@Primary", SqlDbType.Bit).Value = primary;

            int retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        internal DataTable GetMenuTable()
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetMenuTable"));
            cmd.CommandType = CommandType.StoredProcedure;

            DataTable tblMenu = new DataTable("Menus");
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                tblMenu.Load(reader, LoadOption.Upsert);
                reader.Close();
            }

            return tblMenu;
        }

        internal int[] GetUesrPermission(string module, string name, string userName)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetUserPermission"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Module", SqlDbType.VarChar, 50).Value = module;
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = name;
            cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = string.IsNullOrEmpty(userName) ? string.Empty : userName;

            List<int> pValues = new List<int>();
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                while (reader.Read())
                    pValues.Add((int)reader["PValue"]);
                reader.Close();
            }

            return pValues.ToArray();
        }

        internal bool UpdatePage(string module, string name, string description)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("UpdatePage"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Module", SqlDbType.VarChar, 50).Value = module;
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = name;
            cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 500).Value = description;

            int retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        internal void AddPage(string module, string name, string description, int pValue)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("AddPage"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Module", SqlDbType.VarChar, 50).Value = module;
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = name;
            cmd.Parameters.Add("@PValue", SqlDbType.VarChar, 50).Value = pValue;
            cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 500).Value = description;

            int retv = _db.ExecuteNonQuery(cmd);
        }

        internal int AddProfile(string id, string body, bool primary)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("CompanyProfiles_AddProfile"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ID", SqlDbType.VarChar, 50).Value = id;
            cmd.Parameters.Add("@Body", SqlDbType.NVarChar, int.MaxValue).Value = body;
            cmd.Parameters.Add("@Primary", SqlDbType.Bit).Value = primary;

            int retv = _db.ExecuteNonQuery(cmd);

            return retv;
        }

        internal DataRowCollection GetProfileList()
        {
            DataTable tblProfile = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("CompanyProfiles_GetProfileList"));
            cmd.CommandType = CommandType.StoredProcedure;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                tblProfile.Load(reader, LoadOption.Upsert);
                reader.Close();
            }

            return tblProfile.Rows;
        }

        internal string GetFooter()
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetFooter"));
            cmd.CommandType = CommandType.StoredProcedure;

            object content = (string)_db.ExecuteSalar(cmd);

            return content != null ? (string)content : string.Empty;
        }

        internal bool UpdateFooter(string content)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("UpdateFooter"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Content", SqlDbType.NVarChar, 4000).Value = content;
            int retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        internal DataTable GetSupports(int departmentID)
        {
            DataTable tblSupport = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetSupportTable"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblSupport.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleResult));
            }

            return tblSupport;
        }

        internal DataTable GetDepartments()
        {
            DataTable tblDepartment = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetDepartmentTable"));
            cmd.CommandType = CommandType.StoredProcedure;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblDepartment.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleResult));
            }

            return tblDepartment;
        }

        internal DataTable GetNicks(int supportID)
        {
            DataTable tblNick = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetNickTable"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@SupportID", SqlDbType.Int).Value = supportID;
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblNick.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleResult));
            }

            return tblNick;
        }
    }
}
